﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace ORM
{
	/// <summary>
	/// 数据库操作类
	/// </summary>
	public abstract partial class DbOperation : IDisposable
	{
		internal IDbConnection Connection;
		internal IDbTransaction Transaction = null;

		protected DbOperation(IDbConnection connection)
		{
			Connection = connection;
			Connection.Open();

			initShareConcret();
		}

		/// <summary>
		/// 关闭数据库连接并清场
		/// </summary>
		public void Dispose()
		{
			try { Connection.Close(); Connection.Dispose(); } catch { }
		}

		/// <summary>
		/// 针对数据库的实体名称包裹
		/// </summary>
		/// <param name="field_or_table_code">表名或字段名</param>
		/// <returns></returns>
		public abstract string DBCode(string field_or_table_code);

		/// <summary>
		/// Id名称的Sql适配
		/// </summary>
		public string SQL_ID => DBCode(DataObject.FIELD_ID);

		#region 事务适配
		/// <summary>
		/// 开始事务，使用对应数据库框架默认的隔离模式
		/// </summary>
		/// <param name="level"></param>
		public void BeginTransaction(IsolationLevel level = IsolationLevel.Unspecified)
		{
			lock (this)
				if (Transaction == null)
					Transaction = Connection.BeginTransaction(level);
		}

		/// <summary>
		/// 提交事务
		/// </summary>
		public void Commit()
		{
			if (Transaction != null)
			{
				Transaction.Commit();
				Transaction = null;
			}
		}

		/// <summary>
		/// 回滚事务
		/// </summary>
		public void Rollback()
		{
			if (Transaction != null)
			{
				Transaction.Rollback();
				Transaction = null;
			}
		}
		#endregion

		#region 分页
		/// <summary>
		/// 分页(不使用参数查询机制)
		/// </summary>
		/// <param name="obj"></param>
		/// <param name="indexBasedOne"></param>
		/// <param name="perPage"></param>
		/// <param name="sqlWhere"></param>
		/// <param name="orders"></param>
		/// <returns></returns>
		internal abstract IEnumerable<DataObject> PageBySqlWhere(DataObject obj, int indexBasedOne = 1, int perPage = 10, string sqlWhere = null, params (string field, string order)[] orders);

		/// <summary>
		/// 分页(使用参数查询机制)
		/// </summary>
		/// <param name="obj"></param>
		/// <param name="indexBasedOne"></param>
		/// <param name="perPage"></param>
		/// <param name="conditions"></param>
		/// <param name="and_or"></param>
		/// <param name="orders"></param>
		/// <returns></returns>
		internal abstract IEnumerable<DataObject> PageByParameter(DataObject obj, int indexBasedOne = 1, int perPage = 10, IEnumerable<(string field, string op, object value)> conditions = null, string and_or = "and", params (string field, string order)[] orders);
		#endregion

		#region 原生适配(兜底功能)
		/// <summary>
		/// 初始化Command
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		protected abstract IDbCommand initCommand(string sql, params IDataParameter[] parameters);

		/// <summary>
		/// 把Dictionary转换成查询参数
		/// </summary>
		/// <param name="parameters"></param>
		/// <returns></returns>
		protected abstract IEnumerable<IDbDataParameter> ConvertParameters(Dictionary<string, object> parameters);

		/// <summary>
		/// 从select-sql获取数据集
		/// </summary>
		/// <param name="sql">select-sql</param>
		/// <param name="parameters">查询参数</param>
		/// <returns>结果数据集</returns>
		public abstract DataSet DataSetByDataParameter(string sql, params IDataParameter[] parameters);

		/// <summary>
		/// 从select-sql获取数据集
		/// </summary>
		/// <param name="sql">select-sql</param>
		/// <param name="parameters">查询参数</param>
		/// <returns>结果数据集</returns>
		public DataSet DataSetByKV(string sql, Dictionary<string, object> parameters = null)
		=> DataSetByDataParameter(sql, ConvertParameters(parameters)?.ToArray());

		/// <summary>
		/// 从select-sql获取数据表
		/// </summary>
		/// <param name="sql">select-sql</param>
		/// <param name="parameters">查询参数</param>
		/// <returns>结果数据表</returns>
		public DataTable TableByDataParameter(string sql, params IDataParameter[] parameters)
		=> DataSetByDataParameter(sql, parameters).Tables[0];

		/// <summary>
		/// 从select-sql获取数据表
		/// </summary>
		/// <param name="sql">select-sql</param>
		/// <param name="parameters">查询参数</param>
		/// <returns>结果数据表</returns>
		public DataTable TableByKV(string sql, Dictionary<string, object> parameters = null)
		=> DataSetByKV(sql, parameters).Tables[0];

		/// <summary>
		/// 适配ExecuteReader，从select-sql获取单向只读器。用完后记得关闭Reader
		/// </summary>
		/// <param name="sql">select-sql</param>
		/// <param name="behaviour"></param>
		/// <param name="parameters">查询参数</param>
		/// <returns>单向只读器</returns>
		public IDataReader ExecuteReaderByDataParameter(string sql, CommandBehavior? behaviour = null, params IDataParameter[] parameters)
		{
			IDbCommand cmd = initCommand(sql, parameters);
			return behaviour.HasValue ? cmd.ExecuteReader(behaviour.Value) : cmd.ExecuteReader();
		}

		/// <summary>
		/// 适配ExecuteReader，从select-sql获取单向只读器。用完后记得关闭Reader
		/// </summary>
		/// <param name="sql">select-sql</param>
		/// <param name="behaviour"></param>
		/// <param name="parameters">查询参数</param>
		/// <returns>单向只读器</returns>
		public IDataReader ExecuteReaderByKV(string sql, CommandBehavior? behaviour = null, Dictionary<string, object> parameters = null)
		=> ExecuteReaderByDataParameter(sql, behaviour, ConvertParameters(parameters)?.ToArray());

		/// <summary>
		/// 适配ExecuteNonQuery，返回受影响的行数
		/// </summary>
		/// <param name="sql">select-sql</param>
		/// <param name="parameters">查询参数</param>
		/// <returns>受影响的行数</returns>
		public int ExecuteNonQueryByDataParameter(string sql, params IDataParameter[] parameters)
		{
			IDbCommand cmd = initCommand(sql, parameters);
			return cmd.ExecuteNonQuery();
		}

		/// <summary>
		/// 适配ExecuteNonQuery，返回受影响的行数
		/// </summary>
		/// <param name="sql">select-sql</param>
		/// <param name="parameters">查询参数</param>
		/// <returns>受影响的行数</returns>
		public int ExecuteNonQueryByKV(string sql, Dictionary<string, object> parameters = null)
		=> ExecuteNonQueryByDataParameter(sql, ConvertParameters(parameters)?.ToArray());

		/// <summary>
		/// 适配ExecuteScalar，返回首行首列结果
		/// </summary>
		/// <param name="sql">select-sql</param>
		/// <param name="parameters">查询参数</param>
		/// <returns>首行首列结果</returns>
		public object ExecuteScalarByDataParameter(string sql, params IDataParameter[] parameters)
		{
			IDbCommand cmd = initCommand(sql, parameters);
			return cmd.ExecuteScalar();
		}

		/// <summary>
		/// 适配ExecuteScalar，返回首行首列结果
		/// </summary>
		/// <param name="sql">select-sql</param>
		/// <param name="parameters">查询参数</param>
		/// <returns>首行首列结果</returns>
		public object ExecuteScalarByKV(string sql, Dictionary<string, object> parameters = null)
		=> ExecuteScalarByDataParameter(sql, ConvertParameters(parameters)?.ToArray());
		#endregion

		#region 表间关系相关
		/// <summary>
		/// 获取指定表的所有外键
		/// </summary>
		/// <param name="table"></param>
		/// <returns>外键字段名集合</returns>
		internal abstract Dictionary<string, string> ForeignKeys(string table);

		/// <summary>
		/// 获取指定表的子表及相关外键
		/// </summary>
		/// <param name="table">主表</param>
		/// <returns>子表及其相关外键</returns>
		internal abstract Dictionary<string, List<string>> ChildrenTableAndFKs(string table);
		#endregion
	}
}